package com.hippo.core.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author hippo
 */
public class ExcelUtil {

    public static void main(String[] args) {
        try {
            System.out.println(getFieldList("http://rt8zj7vdz.bkt.clouddn.com/21454984160644748a05907c373dadf4.xlsx"));
            System.out.println(getFieldList("http://rt8zj7vdz.bkt.clouddn.com/476b259c53d045f296d3a49504ceddcb.xls"));
            List<Map<String, String>> maps = redExcel("http://rt8zj7vdz.bkt.clouddn.com/21454984160644748a05907c373dadf4.xlsx");
            List<Map<String, String>> maps1 = redExcel("http://rt8zj7vdz.bkt.clouddn.com/476b259c53d045f296d3a49504ceddcb.xls");
            System.out.println(maps);
            System.out.println(maps1);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static ArrayList<String> getFieldList(String filePath) throws Exception {
        File file = UrlToFIle.getFile(filePath);
        if (!file.exists()) {
            throw new Exception("文件不存在!");
        }
        ArrayList<String> list = new ArrayList<>();
        InputStream in = new FileInputStream(file);
        // 读取整个Excel
        HSSFWorkbook sheets = new HSSFWorkbook(in);
        // 获取第一个表单Sheet
        HSSFSheet sheetAt = sheets.getSheetAt(0);
        //默认第一行为标题行，i = 0
        HSSFRow titleRow = sheetAt.getRow(0);
        for (int i = 0; i < titleRow.getPhysicalNumberOfCells(); i++) {
            if (String.valueOf(titleRow.getCell(i)) != "" & String.valueOf(titleRow.getCell(i)) != null) {
                list.add(String.valueOf(titleRow.getCell(i)));
            }
        }

        return list;
    }

    /**
     * 读取excel内容
     * <p>
     * 用户模式下：
     * 弊端：对于少量的数据可以，单数对于大量的数据，会造成内存占据过大，有时候会造成内存溢出
     * 建议修改成事件模式
     */
    public static List<Map<String, String>> redExcel(String filePath) throws Exception {
        File file = UrlToFIle.getFile(filePath);
        if (!file.exists()) {
            throw new Exception("文件不存在!");
        }
        ArrayList<Map<String, String>> list = new ArrayList<>();
        InputStream in = new FileInputStream(file);
        // 读取整个Excel
        HSSFWorkbook sheets = new HSSFWorkbook(in);
        // 获取第一个表单Sheet
        HSSFSheet sheetAt = sheets.getSheetAt(0);
        //默认第一行为标题行，i = 0
        HSSFRow titleRow = sheetAt.getRow(0);
        // 循环获取每一行数据
        for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            HSSFRow row = sheetAt.getRow(i);
            LinkedHashMap<String, String> map = new LinkedHashMap<>();
            // 读取每一格内容
            for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                HSSFCell titleCell = titleRow.getCell(index);
                HSSFCell cell = row.getCell(index);
                cell.setCellType(1);
                if (cell.getStringCellValue().equals("")) {
                    continue;
                }
                map.put(String.valueOf(titleCell), String.valueOf(cell));
            }
            if (map.isEmpty()) {
                continue;
            }
            list.add(map);
        }

        return list;
    }
}